﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.SECURITY.MODEL;

namespace KPIS.GERP.SECURITY.DAL
{
    public class PermissionDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public string Test(int sleep = 0)
        {
            string message = String.Empty;

            try
            {
                dbManager.Open();

                string textToWrite = DateTime.Now.ToShortDateString() + " " + 
                    DateTime.Now.Hour.ToString() + ":" +
                    DateTime.Now.Minute.ToString() + ":" +
                    DateTime.Now.Second.ToString() + "." + 
                    DateTime.Now.Millisecond.ToString();

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@text", textToWrite);

                string strSql = @"INSERT INTO testtbl (text) VALUES (@text)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSql);

                strSql = "select last_insert_id() as last_seq from testtbl";

                System.Threading.Thread.Sleep(sleep);

                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0].Rows[0]["last_seq"].ToString();

                message = textToWrite + " got seq = " + strSeq;

                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return message;
        }

        public DataTable LoadPermission(Nullable<int> Sequence, Nullable<int> Role, string SystemId)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@Role", Role);
                dbManager.AddParameters(3, "@SystemId", SystemId);

                strSQL = "SELECT t.* FROM("
                    + " SELECT p.PERMISSION_SEQ, p.ROLE_SEQ, p.MENU_ITEM_SEQ, p.PERMISSION_TYPE"
                    + ", r.ROLE_NAME, m.MENU_ITEM_NAME"
                    + ", CASE WHEN p.PERMISSION_TYPE = 'R' THEN 1 ELSE 0 END AS PER_READ"
                    + ", CASE WHEN p.PERMISSION_TYPE = 'W' THEN 1 ELSE 0 END AS PER_WRITE"
                    + ", CASE WHEN IsNULL(p.PERMISSION_TYPE, 'N') = 'N' THEN 1 ELSE 0 END AS PER_NON"
                    + ", m.system_id menu_item_system"
                    + " FROM SYS_SC_PERMISSIONS p"
                    + " LEFT JOIN SYS_SC_ROLES r ON p.ROLE_SEQ = r.ROLE_SEQ"
                    + " LEFT JOIN SYS_MENU_ITEMS_LIST m ON p.MENU_ITEM_SEQ = m.MENU_ITEM_SEQ"
                    + " WHERE p.RECORD_STATUS = @ActiveRecord"
                    + " AND r.RECORD_STATUS = @ActiveRecord"
                    + " AND m.RECORD_STATUS = @ActiveRecord"
                    + (Sequence.HasValue ? " AND p.PERMISSION_SEQ = @Sequence" : "")
                    + (Role.HasValue ? " AND p.ROLE_SEQ = @Role" : "")

                    + " UNION ALL"

                    + " SELECT NULL AS PERMISSION_SEQ, NULL AS ROLE_SEQ, MENU_ITEM_SEQ, NULL AS PERMISSION_TYPE"
                    + ", NULL AS ROLE_NAME, MENU_ITEM_NAME"
                    + ", 0 AS PER_READ"
                    + ", 0 AS PER_WRITE"
                    + ", 1 AS PER_NON"
                    + ", SYS_MENU_ITEMS_LIST.system_id as  menu_item_system"
                    + " FROM SYS_MENU_ITEMS_LIST"
                    + " WHERE RECORD_STATUS = @ActiveRecord"
                    + (!String.IsNullOrEmpty(SystemId) ? " and system_id = @SystemId" : "")
                    + ") t "
                    //+ (!String.IsNullOrEmpty(SystemId) ? " where menu_item_system = @SystemId" : "")
                    + " order by t.menu_item_system ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable SelectRoleMenuTreeView(int RoleSequence)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@RoleSequence", RoleSequence);

                strSQL = "select p.permission_seq, p.role_seq, p.menu_seq, p.permission_type"
                    + ", m.menu_id, m.menu_name, m.system, m.url, r.role_name, r.remarks, m.upper_menu_seq"
                    + ", case when p.permission_type = 'A' then 'Approve'"
                        + " else case when p.permission_type = 'W' then 'Read & Write'"
                            + " else 'Read Only'"
                            + " end"
                        + " end AS permission_type_name"
                    + " from gerp_security.permissions p"
                    + " left join gerp_security.roles r on p.role_seq = r.role_seq"
                    + " left join gerp_security.menu m on p.menu_seq = m.menu_seq"
                        + " and r.system = m.system"
                    + " where p.record_status <> @DeleteRecord"
                        + " and r.record_status <> @DeleteRecord"
                        + " and m.record_status <> @DeleteRecord"
                        + " and p.permission_type <> 'N'"
                        + " and p.role_seq = @RoleSequence";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        //public DataTable CheckValid(string role_seq, string menu_seq)
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select *"
        //            + " from SYS_SC_PERMISIONS"
        //            + " where record_status <> 'D'"
        //            + " and role_seq = " + Misc.ValueForSQL(role_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //            + " and menu_seq = " + Misc.ValueForSQL(menu_seq, Misc.DATA_TYPE_HINT.NUMERIC);

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        public void Insert(int RoleSequence, int MenuSequence, string PermissionType, int CreatedBy)
        {            
            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(1, "@MenuSequence", MenuSequence);
                dbManager.AddParameters(2, "@PermissionType", PermissionType);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO SYS_SC_PERMISSIONS"
                    + "(ROLE_SEQ, MENU_ITEM_SEQ, PERMISSION_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@RoleSequence, @MenuSequence, @PermissionType, @RecordStatus, @CreatedBy, @CreatedWhen)";
                
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int Sequence, int RoleSequence, int MenuSequence, string PermissionType, int UpdatedBy)
        {
           try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(2, "@MenuSequence", MenuSequence);
                dbManager.AddParameters(3, "@PermissionType", PermissionType);
                dbManager.AddParameters(4, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(5, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_PERMISSIONS"
                    + " SET ROLE_SEQ = @RoleSequence"
                        + ", MENU_ITEM_SEQ = @MenuSequence"
                        + ", PERMISSION_TYPE = @PermissionType"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE PERMISSION_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_PERMISSIONS"
                    + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE PERMISSION_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
